
--msg-num = -1       
--msg-num = -2     
--msg-num = -3            
--msg-num = -4            
--msg-num = -5  
-------------------------------------------------------------------------
--   
--@Msg_Num=-10        
------------------------------------------------------------

Create PROCEDURE [dbo].[Sodor_Factor]
    @msg_num NUMERIC(18, 0) OUTPUT,
    @TmpId NUMERIC(18, 0)
AS

-----------------------------------Create Virtual TmpHeadh-------------------------------------------------------------------------
DECLARE @TmpHeadh AS TABLE
(
    Id_Havaleh NUMERIC(18, 0),
    Kind_Flag CHAR(1),
    Nu_Havaleh VARCHAR(8),
    Nu_Resid VARCHAR(8),
    Nu_Forosh VARCHAR(8),
    Nu_Kharid VARCHAR(8),
    Nu_Bargasht_Forosh VARCHAR(8),
    Nu_Bargasht_kharid VARCHAR(8),
    Items SMALLINT,
    Recive_Name NVARCHAR(35),
    Trans_Name NVARCHAR(35),
    Tot_Price_KHarid MONEY,
    Tot_Price_forosh MONEY,
    Tot_Price_Omdeh MONEY,
    Tot_Price_Cooperative MONEY,
    Note NTEXT,
    User_id SMALLINT,
    Tarikh_faktor SMALLDATETIME,
    Tarikh_Pardakht SMALLDATETIME,
    CheckTarikh_Pardakht CHAR(1),
    Number_bar VARCHAR(15),
    Sanad INT,
    Store_Code VARCHAR(3),
    Kind_Cooperative SMALLINT,
    UserReciver SMALLINT
);

INSERT INTO @TmpHeadh
(
    Id_Havaleh,
    Kind_Flag,
    Nu_Havaleh,
    Nu_Resid,
    Nu_Forosh,
    Nu_Kharid,
    Nu_Bargasht_Forosh,
    Items,
    Nu_Bargasht_kharid,
    Recive_Name,
    Trans_Name,
    Tot_Price_KHarid,
    Tot_Price_forosh,
    Tot_Price_Omdeh,
    Tot_Price_Cooperative,
    Note,
    User_id,
    Tarikh_faktor,
    Tarikh_Pardakht,
    CheckTarikh_Pardakht,
    Number_bar,
    Sanad,
    Store_Code,
    Kind_Cooperative,
    UserReciver
)
SELECT Id_Havaleh,
       Kind_Flag,
       Nu_Havaleh,
       Nu_Resid,
       Nu_Forosh,
       Nu_Kharid,
       Nu_Bargasht_Forosh,
       Items,
       Nu_Bargasht_kharid,
       Recive_Name,
       Trans_Name,
       Tot_Price_KHarid,
       Tot_Price_forosh,
       Tot_Price_Omdeh,
       Tot_Price_Cooperative,
       Note,
       User_id,
       Tarikh_faktor,
       Tarikh_Pardakht,
       CheckTarikh_Pardakht,
       Number_bar,
       Sanad,
       Store_Code,
       Kind_Cooperative,
       UserReciver
FROM dbo.TmpHeadH
WHERE Id_Havaleh = @TmpId;

-----------------------------------Create Virtual TmpHavaleh-------------------------------------------------------------------------
DECLARE @TmpHavaleh AS TABLE
(
    Id_Havaleh NUMERIC(18, 0),
    AutoId BIGINT,
    Sender VARCHAR(5),
    Reciver VARCHAR(8),
    Radif SMALLINT,
    K_Code VARCHAR(15),
    K_Qty1 MONEY,
    K_Qty2 MONEY,
    R_Unit1_2 MONEY,
    Price_Kharid MONEY,
    Price_Forosh MONEY,
    Price_Omdeh MONEY,
    Status SMALLINT,
    Takhfif_Flag CHAR(1),
    Cooperative_Flag CHAR(1),
    Point_Flag BIT,
    Serial_Flag BIT,
    Exp_Flag BIT,
    Hamrah_Flag BIT,
    EditQty_Flag BIT,
    CtrMojodi CHAR(1),
    Link_Record BIGINT,
    Make_Flag BIT,
    Note NVARCHAR(250),
    DayOfKala SMALLINT,
    Price_Consumer MONEY,
    Price_Finish MONEY,
    Tot_Kharid MONEY,
    Tot_Forosh MONEY,
    Tot_Omdeh MONEY
);

INSERT INTO @TmpHavaleh
(
    Id_Havaleh,
    AutoId,
    Sender,
    Reciver,
    Radif,
    K_Code,
    K_Qty1,
    K_Qty2,
    R_Unit1_2,
    Price_Kharid,
    Price_Forosh,
    Price_Omdeh,
    Status,
    Takhfif_Flag,
    Cooperative_Flag,
    Point_Flag,
    Serial_Flag,
    Exp_Flag,
    Hamrah_Flag,
    EditQty_Flag,
    CtrMojodi,
    Link_Record,
    Make_Flag,
    Note,
    DayOfKala,
    Price_Consumer,
    Price_Finish,
    Tot_Kharid,
    Tot_Forosh,
    Tot_Omdeh
)
SELECT Id_Havaleh,
       AutoId,
       Sender,
       Reciver,
       Radif,
       K_Code,
       K_Qty1,
       K_Qty2,
       R_Unit1_2,
       Price_KHarid,
       Price_Forosh,
       Price_Omdeh,
       Status,
       Takhfif_Flag,
       Cooperative_Flag,
       Point_Flag,
       Serial_Flag,
       Exp_Flag,
       Hamrah_Flag,
       EditQty_Flag,
       CtrMojodi,
       Link_Record,
       Make_Flag,
       Note,
       DayOfKala,
       Price_Consumer,
       Price_Finish,
       Tot_Kharid,
       Tot_Forosh,
       Tot_Omdeh
FROM dbo.TmpHavaleh
WHERE Id_Havaleh = @TmpId;

UPDATE TmpHeadh
SET Tot_Price_KHarid = T.TotPriceKharid,
    Tot_Price_forosh = T.TotPriceForosh,
    Tot_Price_Omdeh = T.TotPriceOmdeh,
    Tot_Price_Cooperative = CASE
                                WHEN Kind_Flag IN ( 2, 3, 4, 8 ) THEN
                                    PriceKharidCooperative
                                WHEN Kind_Flag IN ( 5, 7, 9, 0, 6 ) THEN
                                    PriceForoshCooperative
                            END,
    Kind_Cooperative = CASE
                           WHEN T.KindCooperative > 0 THEN
                               CAST(Kind_Flag AS TINYINT) + 900
                           ELSE
                               0
                       END
FROM @TmpHeadh TmpHeadh
    INNER JOIN
    (
        SELECT Id_Havaleh,
               SUM(Tot_Kharid) TotPriceKharid,
               SUM(Tot_Forosh) TotPriceForosh,
               SUM(Tot_Omdeh) TotPriceOmdeh,
               SUM(   CASE
                          WHEN ISNULL(Cooperative_Flag, 0) = '1' THEN
                              ROUND(K_Qty1 * Price_Kharid, 0)
                          ELSE
                              0
                      END
                  ) PriceKharidCooperative,
               SUM(   CASE
                          WHEN ISNULL(Cooperative_Flag, 0) = '1' THEN
                              ROUND(K_Qty1 * Price_Forosh, 0)
                          ELSE
                              0
                      END
                  ) PriceForoshCooperative,
               SUM(CAST(ISNULL(Cooperative_Flag, 0) AS INT)) KindCooperative
        FROM @TmpHavaleh
        WHERE Id_Havaleh = @TmpId
        GROUP BY Id_Havaleh
    ) AS T
        ON TmpHeadh.Id_Havaleh = T.Id_Havaleh
WHERE TmpHeadh.Id_Havaleh = @TmpId;
-----------------------------------------------------------------------------------------------------------------------------------------                         

IF
(
    SELECT TOP 1 Id_Havaleh FROM @TmpHavaleh WHERE Id_Havaleh = @TmpId
) > 0
BEGIN
    SET @msg_num = 0;
    --    
    DECLARE @Nu_sanad BIGINT;
    DECLARE @idSanad BIGINT;
    DECLARE @Lock BIT;

    SELECT @idSanad = Sanad
    FROM @TmpHeadh
    WHERE Id_Havaleh = @TmpId;
    SET @Nu_sanad = CASE
                        WHEN @idSanad = 0 THEN
                        (
                            SELECT Nu_Manual
                            FROM HeadS
                            WHERE DATEDIFF(DAY, Tarikh_User, GETDATE()) = 0
                                  AND Auto = 1
                        )
                        ELSE
                    (
                        SELECT Nu_Manual FROM HeadS WHERE Id_Sanad = @idSanad
                    )
                    END;
    SET @Lock = ISNULL(   CASE
                              WHEN @idSanad = 0 THEN
                              (
                                  SELECT Fiexd
                                  FROM HeadS
                                  WHERE DATEDIFF(DAY, Tarikh_User, GETDATE()) = 0
                                        AND Auto = 1
                              )
                              ELSE
                          (
                              SELECT Fiexd FROM HeadS WHERE Id_Sanad = @idSanad
                          )
                          END,
                          0
                      );
    IF (@Lock = 1)
    BEGIN
        SET @msg_num = CAST('-99' + LTRIM(STR(@Nu_sanad)) AS INT);
        RETURN @msg_num; --msg-num = -10    
    END;
    ------------------------------------------------------------------------------------------------
    --   
    IF NOT EXISTS
    (
        SELECT 1
        FROM sys.sysobjects
        WHERE name = 'TmpDrugh'
              AND xtype = 'U'
    )
    BEGIN
        IF EXISTS
        (
            SELECT TOP 1
                   TmpHavaleh.K_Code
            FROM dbo.Anbar
                INNER JOIN @TmpHavaleh AS TmpHavaleh
                    ON dbo.Anbar.A_Code = TmpHavaleh.Sender
                       AND dbo.Anbar.K_Code = TmpHavaleh.K_Code
            GROUP BY TmpHavaleh.Sender,
                     TmpHavaleh.K_Code,
                     dbo.Anbar.Mojodi,
                     TmpHavaleh.CtrMojodi,
                     TmpHavaleh.Id_Havaleh
            HAVING (TmpHavaleh.CtrMojodi = '1')
                   AND (TmpHavaleh.Id_Havaleh = @TmpId)
                   AND (dbo.Anbar.Mojodi - SUM(TmpHavaleh.K_Qty1) < 0)
                   AND (LEN(TmpHavaleh.Sender) = 3)
        )
        BEGIN
            SET @msg_num = -1; --msg-num = -1       
            RETURN @msg_num;
        END;
    END;
    ELSE
     IF  EXISTS
    (
        SELECT 1
        FROM sys.sysobjects
        WHERE name = 'TmpDrugh'
              AND xtype = 'U'
    )begin

        IF EXISTS
        (
            SELECT TOP 1
                   TmpHavaleh.K_Code
            FROM dbo.Anbar
                INNER JOIN @TmpHavaleh AS TmpHavaleh
                    ON dbo.Anbar.A_Code = TmpHavaleh.Sender
                       AND dbo.Anbar.K_Code = TmpHavaleh.K_Code
                OUTER  APPLY
            (
                SELECT SUM(D.K_Qty1) ReserveCount
                FROM dbo.TmpDrugH H WITH (NOLOCK)
                    JOIN dbo.TmpDrugHavaleh D
                        ON H.Id_Havaleh = D.Id_Havaleh
                          AND H.Kind_Flag = 1 
						   AND H.kind_Save = 2
                           AND D.Sender = TmpHavaleh.Sender
                           AND D.K_Code = TmpHavaleh.K_Code
                GROUP BY D.K_Code
            ) AS drug
            GROUP BY TmpHavaleh.Sender,
                     TmpHavaleh.K_Code,
                     dbo.Anbar.Mojodi,
                     TmpHavaleh.CtrMojodi,
                     TmpHavaleh.Id_Havaleh
            HAVING (TmpHavaleh.CtrMojodi = '1')
                   AND (TmpHavaleh.Id_Havaleh = @TmpId)
                   AND (dbo.Anbar.Mojodi - (SUM(TmpHavaleh.K_Qty1) + SUM(ISNULL(drug.ReserveCount,0))) < 0)
                   AND (LEN(TmpHavaleh.Sender) = 3)
        )
        BEGIN
            SET @msg_num = -1; --msg-num = -1       
            RETURN @msg_num;
        END;
    END;
    ------------------------------------------------------------------------------------------------
    --  
    IF
    (
        SELECT COUNT(*)FROM Less_Serial(@TmpId)
    ) > 0
    BEGIN
        SET @msg_num = -3; --msg-num = -3            
        RETURN @msg_num;
    END;
    ------------------------------------------------------------------------------------------------
    --   
    ELSE IF
    (
        SELECT COUNT(*)FROM Less_Exp(@TmpId)
    ) > 0
    BEGIN
        SET @msg_num = -4; --msg-num = -4            
        RETURN @msg_num;
    END;

    ------------------------------------------------------------------------------------------------
    ELSE
    BEGIN
        BEGIN TRAN;
        DECLARE @Last_Id NUMERIC(18, 0);
        INSERT INTO dbo.headH
        (
            Kind_Flag,
            Nu_Havaleh,
            Nu_Resid,
            Nu_Forosh,
            Nu_Kharid,
            Nu_Bargasht_Forosh,
            Nu_Bargasht_kharid,
            Items,
            Recive_Name,
            Trans_Name,
            Tot_Price_KHarid,
            Tot_Price_forosh,
            Tot_Price_Omdeh,
            Tot_Price_Cooperative,
            Note,
            User_id,
            Tarikh_faktor,
            Tarikh_Pardakht,
            CheckTarikh_Pardakht,
            Number_bar,
            Sanad,
            Store_Code,
            Kind_Cooperative,
            UserReciver
        )
        SELECT Kind_Flag,
               Nu_Havaleh,
               Nu_Resid,
               Nu_Forosh,
               Nu_Kharid,
               Nu_Bargasht_Forosh,
               Nu_Bargasht_kharid,
               Items,
               Recive_Name,
               Trans_Name,
               Tot_Price_KHarid,
               Tot_Price_forosh,
               Tot_Price_Omdeh,
               Tot_Price_Cooperative,
               Note,
               User_id,
               Tarikh_faktor,
               Tarikh_Pardakht,
               CheckTarikh_Pardakht,
               Number_bar,
               Sanad,
               Store_Code,
               Kind_Cooperative,
               UserReciver
        FROM @TmpHeadh
        WHERE Id_Havaleh = @TmpId;
        IF @@Error > 0
        BEGIN
            SET @msg_num = -5;
            ROLLBACK TRANSACTION;
            RETURN @msg_num;
        END;

        SELECT @Last_Id = SCOPE_IDENTITY();


        INSERT INTO dbo.Havaleh
        (
            Id_Havaleh,
            AutoId,
            Sender,
            Reciver,
            Radif,
            K_Code,
            K_Qty1,
            K_Qty2,
            R_Unit1_2,
            Price_KHarid,
            Price_Forosh,
            Price_Omdeh,
            Status,
            Takhfif_flag,
            Cooperative_Flag,
            Point_Flag,
            Serial_Flag,
            Hamrah_Flag,
            EditQty_Flag,
            CtrMojodi,
            link_Record,
            Exp_Flag,
            Make_Flag,
            Note,
            DayOfKala,
            Price_Consumer,
            Price_Finish,
            Avg_Price
        )
        SELECT @Last_Id,
               AutoId,
               Sender,
               Reciver,
               Radif,
               K_Code,
               K_Qty1,
               K_Qty2,
               R_Unit1_2,
               Price_Kharid,
               Price_Forosh,
               Price_Omdeh,
               Status,
               Takhfif_Flag,
               Cooperative_Flag,
               Point_Flag,
               Serial_Flag,
               Hamrah_Flag,
               EditQty_Flag,
               CtrMojodi,
               Link_Record,
               Exp_Flag,
               Make_Flag,
               Note,
               DayOfKala,
               Price_Consumer,
               Price_Finish,
               Price_Kharid
        FROM @TmpHavaleh
        WHERE Id_Havaleh = @TmpId; -- ј         

        IF @@Error > 0
        BEGIN
            SET @msg_num = -5;
            ROLLBACK TRANSACTION;
            RETURN @msg_num;
        END;
        ------------------------------------------------------------------------------------------------    --   
        IF
        (
            SELECT TOP 1
                   Id_Havaleh
            FROM @TmpHavaleh
            WHERE (Id_Havaleh = @TmpId)
                  AND (Exp_Flag = 1)
        ) > 0
            EXEC Sodor_Exp @TmpId;
        IF @@Error > 0
        BEGIN
            SET @msg_num = -5;
            ROLLBACK TRANSACTION;
            RETURN @msg_num;
        END;
        ------------------------------------------------------------------------------------------------
        IF
        (
            SELECT TOP 1
                   Id_Serial
            FROM dbo.TmpSerial
            WHERE AutoId IN
                  (
                      SELECT AutoId FROM @TmpHavaleh WHERE Id_Havaleh = @TmpId
                  )
        ) > 0
            EXEC Sodor_Serial @TmpId;
        IF @@Error > 0
        BEGIN
            SET @msg_num = -5;
            ROLLBACK TRANSACTION;
            RETURN @msg_num;
        END;
        ------------------------------------------------------------------------------------------------
        DELETE FROM dbo.Tmp_Over_Under_Factor
        WHERE (Id_Havaleh = @TmpId)
              AND
              (
                  Price <= 0
                  OR Price IS NULL
              );
        IF
        (
            SELECT TOP 1
                   Id_Havaleh
            FROM dbo.Tmp_Over_Under_Factor
            WHERE (Id_Havaleh = @TmpId)
        ) > 0
        BEGIN
            INSERT INTO dbo.Over_Under_Factor
            (
                Id_Havaleh,
                Code_Over_Under,
                Type,
                Price,
                [Percent],
                Act_Code,
                Auto_Id_Havaleh,
                Price_Row,
                Guy_Code
            )
            SELECT @Last_Id,
                   Code_Over_Under,
                   Type,
                   Price,
                   [Percent],
                   Act_Code,
                   Auto_id_Havaleh,
                   Price_Row,
                   Guy_Code
            FROM Tmp_Over_Under_Factor
            WHERE (Id_Havaleh = @TmpId);
            IF @@Error > 0
            BEGIN
                SET @msg_num = -5;
                ROLLBACK TRANSACTION;
                RETURN @msg_num;
            END;
        END;

        ------------------------------------------------------------------------------------------------
        INSERT INTO dbo.Over_Under_Row
        (
            AutoId,
            Name,
            Code_Act,
            Type,
            Price,
            [Percent],
            Code_Over_Under
        )
        SELECT Tmp_Over_Under_Row.AutoId,
               Tmp_Over_Under_Row.Name,
               Tmp_Over_Under_Row.Code_Act,
               Tmp_Over_Under_Row.Type,
               Tmp_Over_Under_Row.Price,
               Tmp_Over_Under_Row.[Percent],
               Tmp_Over_Under_Row.Code_Over_Under
        FROM @TmpHavaleh TmpHavaleh
            INNER JOIN Tmp_Over_Under_Row
                ON TmpHavaleh.AutoId = Tmp_Over_Under_Row.AutoId
        WHERE TmpHavaleh.Id_Havaleh = @TmpId
              AND Price > 0;
        IF @@Error > 0
        BEGIN
            SET @msg_num = -5;
            ROLLBACK TRANSACTION;
            RETURN @msg_num;
        END;
        ---------------------------------------------------------------------------------------------------------------------------------------
        DECLARE @Id_Cheqe BIGINT;
        SELECT @Id_Cheqe = dbo.TmpHeadC.Id_Cheqe
        FROM dbo.TmpHeadC
            INNER JOIN dbo.TmpCheqe
                ON dbo.TmpHeadC.Id_Cheqe = dbo.TmpCheqe.Id_Cheqe
        WHERE (dbo.TmpHeadC.Id_Havaleh = @TmpId)
              AND (dbo.TmpCheqe.Ch_Price > 0);

        IF (@Id_Cheqe <> NULL)
           OR (@Id_Cheqe <> 0)
            EXEC @msg_num = Sodor_Vajeh @msg_num,
                                        @Id_Cheqe,
                                        @TmpId,
                                        @Last_Id,
                                        @idSanad;
        IF @msg_num < 0
        BEGIN
            SET @msg_num = -5;
            ROLLBACK TRANSACTION;
            RETURN @msg_num;
        END;
        ------------------------------------------------------------------------------------------------
        DELETE FROM dbo.TmpHeadH
        WHERE Id_Havaleh = @TmpId;
        DELETE FROM dbo.TmpHeadC
        WHERE Id_Havaleh = @TmpId;
        ------------------------------------------------------------------------------------------------
        UPDATE Havaleh
        SET Avg_Price = Price_KHarid,
            Tot_Omdeh = Price_KHarid
        FROM Havaleh
            INNER JOIN headH
                ON headH.Id_Havaleh = Havaleh.Id_Havaleh
        WHERE dbo.headH.Id_Havaleh = @Last_Id
              AND Kind_Flag IN ( 3, 4 )
              AND Avg_Price = 0;

        COMMIT TRAN;
        SELECT @msg_num = @Last_Id;
        RETURN @msg_num;
        PRINT 'ǘ  ';
    END;
END;
ELSE
BEGIN
    SET @msg_num = -2;
    RETURN @msg_num;
    PRINT '    ';
END
